package bktech.soict.hust.bknote.activity.sms;

import java.io.File;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class SmsHelper extends SQLiteOpenHelper {
	private static final String DATABASE_NAME = "iMessenger.db";
	private static final int SCHEMA_VERSION = 1;

	public SmsHelper(Context context) {
		super(context, DATABASE_NAME, null, SCHEMA_VERSION);
		
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		db.execSQL("CREATE TABLE inbox (_id INTEGER PRIMARY KEY AUTOINCREMENT, thread_id INTEGER, address TEXT, date INTEGER, body TEXT );");
		db.execSQL("CREATE TABLE contact (_id INTEGER PRIMARY KEY AUTOINCREMENT, number TEXT, display_name TEXT, contact_id INTEGER, priority INTEGER);");
	}
	public void insert_inbox(int thread_id, String address, long date, String body){
		ContentValues cv = new ContentValues();
		cv.put("thread_id", thread_id);
		cv.put("address", address);
		cv.put("date", date);
		cv.put("body", body);
		getWritableDatabase().insert("inbox", "thread_id", cv);
	}
	public void insert_contact(String number, String display_name, int contact_id, int priority){
		ContentValues cv = new ContentValues();
		cv.put("number", number);
		cv.put("display_name", display_name);
		cv.put("contact_id", contact_id);
		cv.put("priority", priority);
		getWritableDatabase().insert("contact", "display_name", cv);
	}
	public Cursor queryInbox(){
		return(getWritableDatabase().rawQuery("SELECT * FROM (SELECT * FROM inbox LEFT JOIN contact ON inbox.address = contact.number ORDER BY date) GROUP BY address ORDER BY date DESC", null));	 
	}
	public Cursor queryConversation(String address){
		String sql = "SELECT* FROM(SELECT * FROM inbox LEFT JOIN contact ON inbox.address = contact.number) WHERE address = \"" +address+ "\" ORDER BY date ";
		return(getWritableDatabase().rawQuery(sql, null));
	}
	public Cursor queryContact(){
		return(getWritableDatabase().rawQuery("SELECT * FROM contact ORDER BY display_name", null));
	}
	public Cursor querySearch(){
		return(getWritableDatabase().rawQuery("SELECT* FROM inbox LEFT JOIN contact ON contact.number = inbox.address", null));
	}
	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		db.execSQL("DROP TABLE IF EXISTS "  + "contact");
        db.execSQL("DROP TABLE IF EXISTS "  + "inbox");
        onCreate(db);
	}
	

}
